using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
   public class tbsanphamDAL
    {
       private SqlConnection sqlCn = null;
        static public string error = "";
        public tbsanphamDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            sqlCn = new SqlConnection(stringConnection);
            sqlCn.Open();
        }
       ~tbsanphamDAL()
        {
            //this.sqlCn.Close();
            //this.sqlCn.Dispose();
        }

        public int Insert(tbsanphamDTO sp)
        {
            SqlCommand cm = new SqlCommand("sp_sanpham_Insert", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

           
            //cm.Parameters.AddWithValue("@Masp", sp.Masp);
            cm.Parameters.AddWithValue("@tensp", sp.Tensp);
            cm.Parameters.AddWithValue("@dacdiem", sp.Dacdiem);
            cm.Parameters.AddWithValue("@thongtin", sp.Thongtin);
            cm.Parameters.AddWithValue("@thoathuan", sp.Thoathuan);
            cm.Parameters.AddWithValue("@giaban", sp.Giaban);
            cm.Parameters.AddWithValue("@soluongmax", sp.Soluongmax);
            cm.Parameters.AddWithValue("@maloaisp", sp.Maloaisp);
            cm.Parameters.AddWithValue("@ngaybatdau", sp.Ngaybatdau);
            cm.Parameters.AddWithValue("@ngayketthuc", sp.Ngayketthuc);
            cm.Parameters.AddWithValue("@madn", sp.Madn);
            cm.Parameters.AddWithValue("@matinhthanh", sp.Matinhthanh);
            cm.Parameters.AddWithValue("@giagoc", sp.Giagoc);
            cm.Parameters.AddWithValue("@soluongdaban", sp.Soluongdaban);
            cm.Parameters.AddWithValue("@giatrisudung", sp.Giatrisudung);
            cm.Parameters.AddWithValue("@chietkhau", sp.Chietkhau);
            cm.Parameters.AddWithValue("@matrangthai", sp.Matrangthai);
            cm.ExecuteNonQuery();
           
            string cautruyvan = "select masp from sanpham order by masp desc";
            cm = new SqlCommand(cautruyvan, this.sqlCn);
            SqlDataReader dr = cm.ExecuteReader(CommandBehavior.SingleRow);
            dr.Read();
            int ma=int.Parse(dr["masp"].ToString());
            return ma;
        }

       public void Update(tbsanphamDTO sp)
        {
            SqlCommand cm = new SqlCommand("sp_sanpham_Update", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@masp", sp.Masp);
            cm.Parameters.AddWithValue("@tensp", sp.Tensp);
            cm.Parameters.AddWithValue("@dacdiem", sp.Dacdiem);
            cm.Parameters.AddWithValue("@thongtin", sp.Thongtin);
            cm.Parameters.AddWithValue("@thoathuan", sp.Thoathuan);
            cm.Parameters.AddWithValue("@giaban", sp.Giaban);
            cm.Parameters.AddWithValue("@soluongmax", sp.Soluongmax);
            cm.Parameters.AddWithValue("@maloaisp", sp.Maloaisp);
            cm.Parameters.AddWithValue("@ngaybatdau", sp.Ngaybatdau);
            cm.Parameters.AddWithValue("@ngayketthuc", sp.Ngayketthuc);
            cm.Parameters.AddWithValue("@madn", sp.Madn);
            cm.Parameters.AddWithValue("@matinhthanh", sp.Matinhthanh);
            cm.Parameters.AddWithValue("@giagoc", sp.Giagoc);
            cm.Parameters.AddWithValue("@soluongdaban", sp.Soluongdaban);
            cm.Parameters.AddWithValue("@giatrisudung", sp.Giatrisudung);
            cm.Parameters.AddWithValue("@chietkhau", sp.Chietkhau);
            cm.Parameters.AddWithValue("@matrangthai", sp.Matrangthai);
            try
            {
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return;
            }
        }

        public void Delete(int Ma)
        {
            SqlCommand cm = new SqlCommand("sp_sanpham_Delete", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@masp", Ma);

            cm.ExecuteNonQuery();
        }

        public tbsanphamcollection GET(string Where, int PageIndex, int PageSize, string OrderBy,
                                       string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand("sp_sanpham_GET", this.sqlCn);
            cm.CommandType = CommandType.StoredProcedure;


            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);

            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);


            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);

            tbsanphamcollection spcollection = new tbsanphamcollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                tbsanphamDTO sp = new tbsanphamDTO();
                sp.Masp = int.Parse(ds.Tables[0].Rows[i]["masp"].ToString());
                sp.Tensp = ds.Tables[0].Rows[i]["tensp"].ToString();
                sp.Dacdiem= ds.Tables[0].Rows[i]["dacdiem"].ToString();
                sp.Thongtin = ds.Tables[0].Rows[i]["thongtin"].ToString();
                sp.Thoathuan = ds.Tables[0].Rows[i]["thoathuan"].ToString();
                sp.Giaban = int.Parse(ds.Tables[0].Rows[i]["giaban"].ToString());
                sp.Soluongmax= int.Parse(ds.Tables[0].Rows[i]["soluongmax"].ToString());
                sp.Maloaisp= int.Parse(ds.Tables[0].Rows[i]["maloaisp"].ToString());
                sp.Ngaybatdau = (DateTime)(ds.Tables[0].Rows[i]["ngaybatdau"]);                         
                sp.Ngayketthuc = (DateTime)(ds.Tables[0].Rows[i]["ngayketthuc"]);
                sp.Madn =int.Parse(ds.Tables[0].Rows[i]["madn"].ToString());
                sp.Matinhthanh = int.Parse(ds.Tables[0].Rows[i]["matinhthanh"].ToString());
                sp.Giagoc = int.Parse(ds.Tables[0].Rows[i]["giagoc"].ToString());
                sp.Soluongdaban = int.Parse(ds.Tables[0].Rows[i]["soluongdaban"].ToString());
                sp.Matrangthai = int.Parse(ds.Tables[0].Rows[i]["matrangthai"].ToString());
                sp.Giatrisudung = float.Parse(ds.Tables[0].Rows[i]["giatrisudung"].ToString());
                sp.Chietkhau = int.Parse(ds.Tables[0].Rows[i]["chietkhau"].ToString());                
                spcollection.Add(sp);



            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());

            return spcollection;
        }
    }
}
